'''
Created on 15-05-2012

@author: ahaw
'''
import sqlite3
import stagger
from Settings import *
import fnmatch
class Database(object):
    '''
    Klasa obslugujaca baze danych z informacja o utworach
    '''
    def __init__(self):
        '''
        Tworzenie bazy i laczenie sie z nia
        '''
        print('Database init')
        databaseDir = Settings.instance.configDir + 'baza.sqlite'
        self.conn = sqlite3.connect(databaseDir)
        self.cursor = self.conn.cursor()
        Database.cursor = self.cursor
        Database.connection=self.conn
        Database.instance = self
        try:
            self.cursor.execute('''create table baza
                    (file TEXT, title TEXT, album TEXT, artist TEXT, genre TEXT,start TEXT, stop TEXT)''')
        except:
            print('Database is initiated')
            pass
    def addToDatabase(self, file,start,stop):
        '''
        Dodawanie pliku(wraz z jego tagami) do bazy wraz z czasem rozpoczecia i zatrzymania (standardowo -1)
        '''
        
        print ("TODO: Database.addToDatabase tutaj w parametrze dac liste zamiast pojedynczego elementu")
        self.cursor.execute('SELECT * FROM baza WHERE file=?', [file])
        row = self.cursor.fetchone()
        if row == None:
            tag = stagger.read_tag(file)
            t = file, tag.title, tag.album, tag.artist, tag.genre,start,stop
            print (t)
            self.cursor.execute('insert into baza values (?,?,?,?,?,?,?)', t)

    def saveDatabase(self):
        '''
        Zapisanie bazy danych
        '''
        print ('Database.saveDatabase: usunac')
        Database.connection.commit()
    def searchRecursively(self,rootPath, pattern):
        '''
        Zwraca liste plikow pasujaca do wzorca ktore sa w rootPath i podkatalogach 
        '''
        
        result=[]
        print ('root ', rootPath)
        for root, dirs, files in os.walk(rootPath):
            for filter in pattern:
                for filename in fnmatch.filter(files, filter):
                    file = ( os.path.join(root, filename))
                    result+=[file]
        return result
    def refreshDatabase(self):
        '''
        Czysci i skanuje ponownie wszystkie katalogi w bibliotece.
        '''
        self.cursor.execute('DELETE FROM baza WHERE file=file')
        print (Settings.instance.libraryDirectories)
        for directory in Settings.instance.libraryDirectories:
            print (directory)
            if directory != None:
                tmp=(Database.searchRecursively(Database.instance,directory,['*.mp3']))
                for file in tmp:
                    print (file)
                    self.addToDatabase(file,-1,-1)
        self.connection.commit()
    def cleanDatabase(self):
        '''
        Sprawdza czy pliki obecne w bazie danych sa w dalszym ciagu w swoich lokalizacjach
        '''
        self.cursor.execute('select file,title,album,artist,genre FROM baza WHERE file = file')
        removeList=[]
        for row in Database.cursor:
            if not QtCore.QFile(row[0]).exists():
                removeList.append(row[0])  
        for file in removeList:
            self.removeFromDatabase(file)
        self.saveDatabase()    
    def removeFromDatabase(self, file):
        '''
        Usuwa plik z bazy danych
        '''
        self.cursor.execute('select * from baza where file=?', [file])
        if self.cursor.fetchone != None:
            print ('deleting')
            self.cursor.execute('DELETE FROM baza WHERE file=?', [file])
